﻿using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace Housing
{
    /// <summary>
    /// Summary description for Admissibility
    /// </summary>
    public class Admissibility
    {
        private int _id;
        private int _noid;
        private string _applyname;
        private string _address;
        private string _recievename;
        private string _phone;
        private string _date;
        private int _agree;
        private int _agreecopy;
        private int _indencard;
        private int _indencardcopy;
        private int _census;
        private int _censuscopy;
        private int _income;
        private int _incomecopy;
        private int _property;
        private int _propertycopy;
        private int _gongfang;
        private int _gongfangcopy;
        private int _approval;
        private int _approvalcopy;
        private int _houseown;
        private int _houseowncopy;
        private int _demolition;
        private int _demolitioncopy;
        private int _hire;
        private int _hirecopy;
        private int _marry;
        private int _marrycopy;
        private int _divorce;
        private int _divorcecopy;
        private int _lowesthood;
        private int _lowesthoodcopy;
        private int _help;
        private int _helpcopy;
        private int _poor;
        private int _poorcopy;
        private int _soldier;
        private int _soldiercopy;
        private int _model;
        private int _modelcopy;
        private int _binding;
        private int _bindingcopy;
        private short _checked;
        private short _applyed;
        private string _street;

        public int Adm_id
        {
            get { return _id; }
            set { _id = value; }
        }
        public int Adm_noid
        {
            get { return _noid; }
            set { _noid = Adm_noid; }
        }
        public string Adm_applyname
        {
            get { return _applyname; }
            set { _applyname = value; }
        }
        public string Adm_address
        {
            get { return _address; }
            set { _address = value; }
        }
        public string Adm_recievename
        {
            get { return _recievename; }
            set { _recievename = value; }
        }
        public string Adm_phone
        {
            get { return _phone; }
            set { _phone = value; }
        }
        public string Adm_date
        {
            get { return _date; }
            set { _date = value; }
        }
        public int Adm_agree
        {
            get { return _agree; }
            set { _agree = value; }
        }
        public int Adm_agreecopy
        {
            get { return _agreecopy; }
            set { _agreecopy = value; }
        }
        public int Adm_indencard
        {
            get { return _indencard; }
            set { _indencard = value; }
        }
        public int Adm_indencardcopy
        {
            get { return _indencardcopy; }
            set { _indencardcopy = value; }
        }
        public int Adm_census
        {
            get { return _census; }
            set { _census = value; }
        }
        public int Adm_censuscopy
        {
            get { return _censuscopy; }
            set { _censuscopy = value; }
        }
        public int Adm_income
        {
            get { return _income; }
            set { _income = value; }
        }
        public int Adm_incomecopy
        {
            get { return _incomecopy; }
            set { _incomecopy = value; }
        }
        public int Adm_property
        {
            get { return _property; }
            set { _property = value; }
        }
        public int Adm_propertycopy
        {
            get { return _propertycopy; }
            set { _propertycopy = value; }
        }
        public int Adm_gongfang
        {
            get { return _gongfang; }
            set { _gongfang = value; }
        }
        public int Adm_gongfangcopy
        {
            get { return _gongfangcopy; }
            set { _gongfangcopy = value; }
        }
        public int Adm_approval
        {
            get { return _approval; }
            set { _approval = value; }
        }
        public int Adm_approvalcopy
        {
            get { return _approvalcopy; }
            set { _approvalcopy = value; }
        }
        public int Adm_houseown
        {
            get { return _houseown; }
            set { _houseown = value; }
        }
        public int Adm_houseowncopy
        {
            get { return _houseowncopy; }
            set { _houseowncopy = value; }
        }
        public int Adm_demolition
        {
            get { return _demolition; }
            set { _demolition = value; }
        }
        public int Adm_demolitioncopy
        {
            get { return _demolitioncopy; }
            set { _demolitioncopy = value; }
        }
        public int Adm_hire
        {
            get { return _hire; }
            set { _hire = value; }
        }
        public int Adm_hirecopy
        {
            get { return _hirecopy; }
            set { _hirecopy = value; }
        }
        public int Adm_marry
        {
            get { return _marry; }
            set { _marry = value; }
        }
        public int Adm_marrycopy
        {
            get { return _marrycopy; }
            set { _marrycopy = value; }
        }
        public int Adm_divorce
        {
            get { return _divorce; }
            set { _divorce = value; }
        }
        public int Adm_divorcecopy
        {
            get { return _divorcecopy; }
            set { _divorcecopy = value; }
        }
        public int Adm_lowesthood
        {
            get { return _lowesthood; }
            set { _lowesthood = value; }
        }
        public int Adm_lowesthoodcopy
        {
            get { return _lowesthoodcopy; }
            set { _lowesthoodcopy = value; }
        }
        public int Adm_help
        {
            get { return _help; }
            set { _help = value; }
        }
        public int Adm_helpcopy
        {
            get { return _helpcopy; }
            set { _helpcopy = value; }
        }
        public int Adm_poor
        {
            get { return _poor; }
            set { _poor = value; }
        }
        public int Adm_poorcopy
        {
            get { return _poorcopy; }
            set { _poorcopy = value; }
        }
        public int Adm_soldier
        {
            get { return _soldier; }
            set { _soldier = value; }
        }
        public int Adm_soldiercopy
        {
            get { return _soldiercopy; }
            set { _soldiercopy = value; }
        }
        public int Adm_model
        {
            get { return _model; }
            set { _model = value; }
        }
        public int Adm_modelcopy
        {
            get { return _modelcopy; }
            set { _modelcopy = value; }
        }
        public int Adm_binding
        {
            get { return _binding; }
            set { _binding = value; }
        }
        public int Adm_bindingcopy
        {
            get { return _bindingcopy; }
            set { _bindingcopy = value; }
        }
        public short Adm_checked
        {
            get { return _checked; }
            set { _checked = value; }
        }
        public short Adm_applyed
        {
            get { return _applyed; }
            set { _applyed = value; }
        }
        public string Adm_street
        {
            get { return _street; }
            set { _street = value; }
        }


        public Admissibility()
        {
        }

        public void GetAdmissibilityByAdmId(int adm_id)
        {
            string query = "SELECT * FROM Admissibility WHERE adm_id=" + adm_id;

            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString,
                                                           CommandType.Text, 
                                                           query);

            if (reader.Read())
            {
                Adm_id = (int)reader[0];
                Adm_noid = (int)reader[1];
                Adm_applyname = Utils.GetString(reader[2]);
                Adm_address = Utils.GetString(reader[3]);
                Adm_recievename = Utils.GetString(reader[4]);
                Adm_phone = Utils.GetString(reader[5]);
                Adm_date = Utils.GetNullableDateTime(reader[6]);
                Adm_agree = Utils.GetNullableInt(reader[7]);
                Adm_agreecopy = Utils.GetNullableInt(reader[8]);
                Adm_indencard = Utils.GetNullableInt(reader[9]);
                Adm_indencardcopy = Utils.GetNullableInt(reader[10]);
                Adm_census = Utils.GetNullableInt(reader[11]);
                Adm_censuscopy = Utils.GetNullableInt(reader[12]);
                Adm_income = Utils.GetNullableInt(reader[13]);
                Adm_incomecopy = Utils.GetNullableInt(reader[14]);
                Adm_property = Utils.GetNullableInt(reader[15]);
                Adm_propertycopy = Utils.GetNullableInt(reader[16]);
                Adm_gongfang = Utils.GetNullableInt(reader[17]);
                Adm_gongfangcopy = Utils.GetNullableInt(reader[18]);
                Adm_approval = Utils.GetNullableInt(reader[19]);
                Adm_approvalcopy = Utils.GetNullableInt(reader[20]);
                Adm_houseown = Utils.GetNullableInt(reader[21]);
                Adm_houseowncopy = Utils.GetNullableInt(reader[22]);
                Adm_demolition = Utils.GetNullableInt(reader[23]);
                Adm_demolitioncopy = Utils.GetNullableInt(reader[24]);
                Adm_hire = Utils.GetNullableInt(reader[25]);
                Adm_hirecopy = Utils.GetNullableInt(reader[26]);
                Adm_marry = Utils.GetNullableInt(reader[27]);
                Adm_marrycopy = Utils.GetNullableInt(reader[28]);
                Adm_divorce = Utils.GetNullableInt(reader[29]);
                Adm_divorcecopy = Utils.GetNullableInt(reader[30]);
                Adm_lowesthood = Utils.GetNullableInt(reader[31]);
                Adm_lowesthoodcopy = Utils.GetNullableInt(reader[32]);
                Adm_help = Utils.GetNullableInt(reader[33]);
                Adm_helpcopy = Utils.GetNullableInt(reader[34]);
                Adm_poor = Utils.GetNullableInt(reader[35]);
                Adm_poorcopy = Utils.GetNullableInt(reader[36]);
                Adm_soldier = Utils.GetNullableInt(reader[37]);
                Adm_soldiercopy = Utils.GetNullableInt(reader[38]);
                Adm_model = Utils.GetNullableInt(reader[39]);
                Adm_modelcopy = Utils.GetNullableInt(reader[40]);
                Adm_binding = Utils.GetNullableInt(reader[41]);
                Adm_bindingcopy = Utils.GetNullableInt(reader[42]);
                Adm_checked = (short)Utils.GetNullableInt(reader[43]);
                Adm_applyed = (short)Utils.GetNullableInt(reader[44]);
                Adm_street = Utils.GetString(reader[45]);
            }

            reader.Close();
        }

        public void GetAdmissibilityByAdmNoId(string adm_noid)
        {
            string query = "SELECT * FROM Admissibility WHERE adm_noid=" + adm_noid;

            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString,
                                                           CommandType.Text,
                                                           query);

            if (reader.Read())
            {
                Adm_id = (int)reader[0];
                Adm_noid = (int)reader[1];
                Adm_applyname = Utils.GetString(reader[2]);
                Adm_address = Utils.GetString(reader[3]);
                Adm_recievename = Utils.GetString(reader[4]);
                Adm_phone = Utils.GetString(reader[5]);
                Adm_date = Utils.GetNullableDateTime(reader[6]);
                Adm_agree = Utils.GetNullableInt(reader[7]);
                Adm_agreecopy = Utils.GetNullableInt(reader[8]);
                Adm_indencard = Utils.GetNullableInt(reader[9]);
                Adm_indencardcopy = Utils.GetNullableInt(reader[10]);
                Adm_census = Utils.GetNullableInt(reader[11]);
                Adm_censuscopy = Utils.GetNullableInt(reader[12]);
                Adm_income = Utils.GetNullableInt(reader[13]);
                Adm_incomecopy = Utils.GetNullableInt(reader[14]);
                Adm_property = Utils.GetNullableInt(reader[15]);
                Adm_propertycopy = Utils.GetNullableInt(reader[16]);
                Adm_gongfang = Utils.GetNullableInt(reader[17]);
                Adm_gongfangcopy = Utils.GetNullableInt(reader[18]);
                Adm_approval = Utils.GetNullableInt(reader[19]);
                Adm_approvalcopy = Utils.GetNullableInt(reader[20]);
                Adm_houseown = Utils.GetNullableInt(reader[21]);
                Adm_houseowncopy = Utils.GetNullableInt(reader[22]);
                Adm_demolition = Utils.GetNullableInt(reader[23]);
                Adm_demolitioncopy = Utils.GetNullableInt(reader[24]);
                Adm_hire = Utils.GetNullableInt(reader[25]);
                Adm_hirecopy = Utils.GetNullableInt(reader[26]);
                Adm_marry = Utils.GetNullableInt(reader[27]);
                Adm_marrycopy = Utils.GetNullableInt(reader[28]);
                Adm_divorce = Utils.GetNullableInt(reader[29]);
                Adm_divorcecopy = Utils.GetNullableInt(reader[30]);
                Adm_lowesthood = Utils.GetNullableInt(reader[31]);
                Adm_lowesthoodcopy = Utils.GetNullableInt(reader[32]);
                Adm_help = Utils.GetNullableInt(reader[33]);
                Adm_helpcopy = Utils.GetNullableInt(reader[34]);
                Adm_poor = Utils.GetNullableInt(reader[35]);
                Adm_poorcopy = Utils.GetNullableInt(reader[36]);
                Adm_soldier = Utils.GetNullableInt(reader[37]);
                Adm_soldiercopy = Utils.GetNullableInt(reader[38]);
                Adm_model = Utils.GetNullableInt(reader[39]);
                Adm_modelcopy = Utils.GetNullableInt(reader[40]);
                Adm_binding = Utils.GetNullableInt(reader[41]);
                Adm_bindingcopy = Utils.GetNullableInt(reader[42]);
                Adm_checked = (short)Utils.GetNullableInt(reader[43]);
                Adm_applyed = (short)Utils.GetNullableInt(reader[44]);
                Adm_street = Utils.GetString(reader[45]);
            }

            reader.Close();
        }

        public SqlDataReader GetAdmissibilityById(int id)
        {
            string query = "SELECT * FROM Admissibility WHERE adm_id=" + id;
            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, query);
            return dr;
        }

        public int DeleteAdmissibility(int adm_id)
        {
            string query = "  DELETE FROM Admissibility WHERE adm_id=" + adm_id;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
                                        CommandType.Text, query);
        }

        //public int UpdateAdmissibility(Admissibility admiss)
        //{
        //    string query = "UPDATE Admissibility SET adm_noid=" + admiss.Adm_noid + "," +
        //              "adm_applyname='" + admiss.Adm_applyname + "',adm_address='" + admiss.Adm_address + "',"
        //              + "adm_recievename=" + admiss.Adm_recievename + ",adm_phone=" + admiss.Adm_phone + ","
        //              + "adm_date='" + Utils.GetNullableValue(admiss.Adm_date) + "',"
        //              + "adm_agree=" + admiss.Adm_agree + ",adm_agreecopy=" + admiss.Adm_agreecopy + ","
        //              + "adm_indencard=" + admiss.Adm_indencard + ",adm_indencardcopy=" + admiss.Adm_indencardcopy + ","
        //              + "adm_census=" + admiss.Adm_census + ",adm_censuscopy=" + admiss.Adm_censuscopy + ","
        //              + "adm_income=" + admiss.Adm_income + ",amd_incomecopy=" + admiss.Adm_incomecopy + ","
        //              + "adm_property=" + admiss.Adm_property + ",adm_propertycopy" + admiss.Adm_propertycopy + ","
        //              + "adm_gongfang=" + admiss.Adm_gongfang + ",adm_gongfangcopy=" + admiss.Adm_gongfangcopy + ","
        //              + "adm_approval=" + admiss.Adm_approval + ",adm_approvalcopy=" + admiss.Adm_approvalcopy + ","
        //              + "adm_houseown=" + admiss.Adm_houseown + ",adm_housecopy=" + admiss.Adm_houseowncopy + ","
        //              + "adm_demolition=" + admiss.Adm_demolition + ",adm_demolitioncopy=" + admiss.Adm_demolitioncopy + ","
        //              + "adm_hire=" + admiss.Adm_hire + ",adm_hirecopy=" + admiss.Adm_hirecopy + ","
        //              + "adm_marry=" + admiss.Adm_marry + ",adm_marrycopy=" + admiss.Adm_marrycopy + ","
        //              + "adm_divorce=" + admiss.Adm_divorce + ",adm_divorcecopy=" + admiss.Adm_divorcecopy + ","
        //              + "adm_lowesthood=" + admiss.Adm_lowesthood + ",adm_lowesthoodcopy=" + admiss.Adm_lowesthoodcopy + ","
        //              + "adm_help=" + admiss.Adm_help + ",adm_helpcopy=" + admiss.Adm_helpcopy + ","
        //              + "adm_poor=" + admiss.Adm_poor + ",adm_poorcopy=" + admiss.Adm_poorcopy + ","
        //              + "adm_soldir=" + admiss.Adm_soldier + ",adm_soldiercopy=" + admiss.Adm_soldiercopy + ","
        //              + "adm_model=" + admiss.Adm_model + ",adm_modelcopy=" + admiss.Adm_modelcopy + ","
        //              + "adm_buinding=" + admiss.Adm_binding + ",adm_buindingcopy=" + admiss.Adm_bindingcopy + ","
        //              + "adm_checked=" + admiss.Adm_checked + ",adm_applyed=" + admiss.Adm_applyed + ","
        //              + "adm_street=" + admiss.Adm_street;

        //    return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
        //        CommandType.Text, query);
        //}

        public int UpdateAdmissibility(ArrayList array, int admid)
        {
            string cmdtext = "UPDATE Admissibility SET adm_applyname='" + array[0] + "',adm_address='" + array[1] + "',adm_recievename='" + array[2] + "',adm_phone='" + array[3] + "',adm_date='" + array[4] + "',adm_agree='" + array[5] + "',adm_agreecopy='" + array[6] + "',";
            cmdtext += "adm_indencard='" + array[7] + "',adm_indencardcopy='" + array[8] + "',adm_census='" + array[9] + "',adm_censuscopy='" + array[10] + "',adm_income='" + array[11] + "',adm_incomecopy='" + array[12] + "',adm_property='" + array[13] + "',";
            cmdtext += "adm_propertycopy='" + array[14] + "',adm_gongfang='" + array[15] + "',adm_gongfangcopy='" + array[16] + "',adm_approval='" + array[17] + "',adm_approvalcopy='" + array[18] + "',adm_houseown='" + array[19] + "',adm_houseowncopy='" + array[20] + "',";
            cmdtext += "adm_demolition='" + array[21] + "',adm_demolitioncopy='" + array[22] + "',adm_hire='" + array[23] + "',adm_hirecopy='" + array[24] + "',adm_marry='" + array[25] + "',adm_marrycopy='" + array[26] + "',adm_divorce='" + array[27] + "',";
            cmdtext += "adm_divorcecopy='" + array[28] + "',adm_lowesthood='" + array[29] + "',adm_lowesthoodcopy='" + array[30] + "',adm_help='" + array[31] + "',adm_helpcopy='" + array[32] + "',adm_poor='" + array[33] + "',adm_poorcopy='" + array[34] + "',";
            cmdtext += "adm_soldier='" + array[35] + "',adm_soldiercopy='" + array[36] + "',adm_model='" + array[37] + "',adm_modelcopy='" + array[38] + "',adm_binding='" + array[39] + "',adm_bindingcopy='" + array[40] + "'";
            cmdtext += "WHERE adm_id=" + admid;
            int succ = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
            return succ;
        }

        public DataSet GetAdmissibilityForCheck(string streetname)
        {
//            string query = @" SELECT  adm_id,( select top 1 apply_longid from apply where apply_noid=Admissibility.adm_noid) as applongid,( select top 1 apply_name from apply where apply_noid=Admissibility.adm_noid) as applyname,adm_noid,adm_address,adm_applyname,( select top 1 [apply_time] from apply where apply_noid=Admissibility.adm_noid) as appdate,adm_applyed,(select top 1 apply_id from apply where apply_checkstate<=5 
//                             AND apply_updchecked=1 and apply_noid=Admissibility.adm_noid order by effectdate ) as id FROM Admissibility 
//                             WHERE adm_checked=0 and adm_street='" + streetname + "'";
            string query = @"select adm_id ,adm_noid,adm_address,adm_applyname,adm_applyed, Apply.apply_longid as applongid,Apply.apply_name as applyname,Apply.apply_id as id,Apply.[apply_time] as appdate,Apply.EffectDate from Admissibility left outer join   dbo.Apply
 ON dbo.Admissibility.adm_noid = dbo.Apply.apply_noid  WHERE adm_checked=0 and adm_street='" + streetname + "' and effectDate is not null order by   effectDate desc,cast( right(isnull(apply_longid,0),4) as int ) ";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            return ds;
        }

        public DataSet GetAdmissibilityForCheck(string streetname,string ym)
        {
            //            string query = @" SELECT  adm_id,( select top 1 apply_longid from apply where apply_noid=Admissibility.adm_noid) as applongid,( select top 1 apply_name from apply where apply_noid=Admissibility.adm_noid) as applyname,adm_noid,adm_address,adm_applyname,( select top 1 [apply_time] from apply where apply_noid=Admissibility.adm_noid) as appdate,adm_applyed,(select top 1 apply_id from apply where apply_checkstate<=5 
            //                             AND apply_updchecked=1 and apply_noid=Admissibility.adm_noid order by effectdate ) as id FROM Admissibility 
            //                             WHERE adm_checked=0 and adm_street='" + streetname + "'";
            string query = @"select adm_id ,adm_noid,adm_address,adm_applyname,adm_applyed, Apply.apply_longid as applongid,Apply.apply_name as applyname,Apply.apply_id as id,Apply.[apply_time] as appdate,Apply.EffectDate from Admissibility left outer join   dbo.Apply
 ON dbo.Admissibility.adm_noid = dbo.Apply.apply_noid  WHERE adm_checked=0 and adm_street='" + streetname + "' and effectDate is not null and effectDate=" + ym + " order by   effectDate desc,cast( right(isnull(apply_longid,0),4) as int ) ";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            return ds;
        }

        public bool AddAdmissibility(ArrayList array)
        {
            string query = "INSERT INTO Admissibility VALUES('" + array[0] + "'";
            for (int i = 1; i < 47; i++)
            {
                query += ",'" + array[i] + "'";
            }
            query += ")";
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query));
            return succ;
        }

        //产生受理单号
        public int GenerateNoid()
        {
            string query = "SELECT MAX(adm_noid) FROM Admissibility";
            string str = Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, query));
            string cmdtext = "SELECT MAX(apply_noid) FROM Apply";
            string str1 = Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, cmdtext));
            if (!string.IsNullOrEmpty(str) || !string.IsNullOrEmpty(str1))
            {
                if (string.IsNullOrEmpty(str))
                    str = str1;
                else if (!string.IsNullOrEmpty(str1))
                {
                    if (Convert.ToInt32(str) < Convert.ToInt32(str1))
                    str = str1;
                }
                
                if (str.Substring(0, 4) != DateTime.Now.Year.ToString())
                    return Convert.ToInt32(DateTime.Now.Year.ToString() + "0001");
                else
                    return Convert.ToInt32(str) + 1;
            }
            else
                return Convert.ToInt32(DateTime.Now.Year.ToString() + "0001");
        }

        public bool SetAdmissibilityAdded(string noid)
        {
            string query = "UPDATE Admissibility SET adm_applyed=1 WHERE adm_noid='" + noid + "'";
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query));
            return succ;
        }

        public DataSet GetAdmissibilityUnCommit(string street)
        {
            string query = @"select adm_id ,adm_noid,adm_address,adm_applyname,adm_applyed, Apply.apply_longid as applongid,Apply.apply_name as applyname,Apply.apply_id as id,Apply.[apply_time] as appdate,Apply.EffectDate from Admissibility left outer join   dbo.Apply
 ON dbo.Admissibility.adm_noid = dbo.Apply.apply_noid  WHERE adm_checked=0 and adm_street like'%" + street + "%' and apply_passed=2 and effectDate is not null order by   effectDate desc,cast( right(isnull(apply_longid,0),4) as int ) ";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            return ds;
        }
        public DataSet GetAdmissibilityCommit(string street)
        {
            string query = @"SELECT * FROM Apply WHERE apply_passed=2 AND 
                             apply_street='" + street + "' ";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            return ds;
        }

        public bool SetApplypassed(string noid)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
            conn.Open();
            SqlTransaction sqltc = conn.BeginTransaction();
            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = sqltc;
                cmd.CommandText = "UPDATE Admissibility SET adm_checked=1 WHERE adm_noid=" + noid;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE Apply SET apply_checkstate=1 WHERE apply_noid=" + noid;
                cmd.ExecuteNonQuery();
                sqltc.Commit();
                return true;
            }
            catch
            {
                sqltc.Rollback();
                return false;
            }
        }

        public bool SetApplyUnpass(string noid)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
            conn.Open();
            SqlTransaction sqltc = conn.BeginTransaction();
            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = sqltc;
                cmd.CommandText = "UPDATE Admissibility SET adm_checked=2 WHERE adm_noid=" + noid;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE Apply SET apply_passed=0 WHERE apply_noid=" + noid;
                cmd.ExecuteNonQuery();
                sqltc.Commit();
                return true;
            }
            catch
            {
                sqltc.Rollback();
                return false;
            }
        }

        //街道选择时
        public DataSet GetAdmissibilityForStreetKey(string streetname)
        {
            string query = @"select adm_id ,adm_noid,adm_address,adm_applyname,adm_applyed, Apply.apply_longid as applongid,Apply.apply_name as applyname,Apply.apply_id as id,Apply.[apply_time] as appdate,Apply.EffectDate from Admissibility left outer join   dbo.Apply
 ON dbo.Admissibility.adm_noid = dbo.Apply.apply_noid  WHERE adm_checked=0 and adm_street like'%" + streetname + "%' and effectDate is not null order by   effectDate desc,cast( right(isnull(apply_longid,0),4) as int ) ";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            return ds;
        }

        //街道选择时
        public DataSet GetAdmissibilityByKey(string streetname, string ym, string key, int type)
        {
            string con = string.Empty;
            ym = ym.Replace("全部", "");
            if (type == 0)
            {
                con = " and Apply.apply_longid = '" + key + "'";
            }
            else if (type == 1)
            {
                con = " and  Apply.apply_name like '%" + key + "%'";
            }
            else
            {
                con = string.Empty;
            }
            string query = @"select adm_id ,adm_noid,adm_address,adm_applyname,adm_applyed, Apply.apply_longid as applongid,Apply.apply_name as applyname,Apply.apply_id as id,Apply.[apply_time] as appdate,Apply.EffectDate from Admissibility left outer join   dbo.Apply
 ON dbo.Admissibility.adm_noid = dbo.Apply.apply_noid  WHERE adm_checked=0 and adm_street like '%" + streetname + "%'" + con + " and effectDate is not null and effectDate like '%" + ym + "%' order by   effectDate desc,cast( right(isnull(apply_longid,0),4) as int ) ";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            return ds;
        }
    }

}